package org.alex.samples.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;

import org.alex.samples.model.ExamUserRoleVO;
import org.alex.samples.model.ExamUserVO;
import org.alex.samples.model.UserJDBC;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
@Repository
@Transactional
public class UserJdbcDAOImpl extends BaseHibernateDAO<UserJDBC> implements IUserJdbcDAO {
	@Autowired
	private JdbcTemplate jdbcTemplate;
//	
	@SuppressWarnings("unused")
	private static final String UserJDBC = null;
	@Autowired
	private SessionFactory sessionFactory;

	public Session getSession() {
		return this.sessionFactory.getCurrentSession();

	}
//	
	
	public UserJdbcDAOImpl(){
		
	}
	
	
	private static final class UserMapper implements RowMapper<UserJDBC>{
		public UserJDBC mapRow(ResultSet rs, int rowNum) throws SQLException {
			UserJDBC userJDBC=new UserJDBC();
			userJDBC.setUserId(rs.getString("user_id"));
			userJDBC.setUserName(rs.getString("user_name"));
			return userJDBC;
		}
		
	}
	
	
	public List<UserJDBC> findAllUsers4Mapper(){
		String sql="select * from exam_user";
		List<UserJDBC> userList=jdbcTemplate.query(sql,new UserMapper());
		return userList;
	}
	

	//查询一条数据表功能方法
	public UserJDBC getUserById(String userId){
		String sql=" select * from exam_user where user_id=?";
		return jdbcTemplate.queryForObject(sql,new Object[]{userId},new RowMapper<UserJDBC>(){
			public UserJDBC mapRow(ResultSet rs,int num) throws SQLException{
				UserJDBC userJDBC=new UserJDBC();
				userJDBC.setUserId(rs.getString("user_id"));
				userJDBC.setUserName(rs.getString("user_name"));
				userJDBC.setPassword(rs.getString("password"));
				userJDBC.setGender(rs.getInt("gender"));
				userJDBC.setBirthday(rs.getDate("birthday"));
				userJDBC.setTel(rs.getString("tel"));
				userJDBC.setAddress(rs.getString("address"));
				userJDBC.setEmail(rs.getString("email"));
				//userJDBC.setLastLockTime(rs.getDate("last_lock_time"));
				userJDBC.setLastLockTime(rs.getTimestamp("last_lock_time"));
				userJDBC.setLoginFailCount(rs.getInt("login_fail_count"));
				return userJDBC;
			}
		});
					
	}
	
	
	//根据用户名查询一条数据表功能方法
		public UserJDBC getUserByName(String userName){
			String sql=" select * from exam_user where user_name=?";
			return jdbcTemplate.queryForObject(sql,new Object[]{userName},new RowMapper<UserJDBC>(){
				public UserJDBC mapRow(ResultSet rs,int num) throws SQLException{
					UserJDBC userJDBC=new UserJDBC();
					userJDBC.setUserId(rs.getString("user_id"));
					userJDBC.setUserName(rs.getString("user_name"));
					userJDBC.setPassword(rs.getString("password"));
					userJDBC.setGender(rs.getInt("gender"));
					userJDBC.setBirthday(rs.getDate("birthday"));
					userJDBC.setTel(rs.getString("tel"));
					userJDBC.setAddress(rs.getString("address"));
					userJDBC.setEmail(rs.getString("email"));
					//userJDBC.setLastLockTime(rs.getDate("last_lock_time"));
					userJDBC.setLastLockTime(rs.getTimestamp("last_lock_time"));
					userJDBC.setLoginFailCount(rs.getInt("login_fail_count"));
					return userJDBC;
				}
			});
						
		}
	
	
	//查询一条数据表功能方法
	public ExamUserVO getUserById1(String userId) {
		//Session session=getSession();
		// return(ExamUserVO) session.get(ExamUserVO.class, userId);
		String sql="select * from exam_user where user_id=?";
		return jdbcTemplate.queryForObject(sql,new Object[]{userId},new RowMapper<ExamUserVO>(){
			public ExamUserVO mapRow(ResultSet rs,int num) throws SQLException{
				ExamUserVO examUserVO=new ExamUserVO();
				examUserVO.setUserId(rs.getString("user_id"));
				examUserVO.setUserName(rs.getString("user_name"));
				examUserVO.setPassword(rs.getString("password"));
				examUserVO.setGender(rs.getInt("gender"));
				examUserVO.setBirthday(rs.getDate("birthday"));
				examUserVO.setTel(rs.getString("tel"));
				examUserVO.setAddress(rs.getString("address"));
				//examUserVO.setLastLockTime(rs.getDate("last_lock_time"));
				examUserVO.setLastLockTime(rs.getTimestamp("last_lock_time"));
				examUserVO.setLoginFailCount(rs.getInt("login_fail_count"));
				return examUserVO;
			}
		});
	}
	
	
	
	
	
	//查询全部数据表功能方法	
	public List<UserJDBC> findAllUsers(){
		String sql="SELECT * FROM exam_user WHERE user_id LIKE '%student%'";
		//String sql="select * from exam_user where user_id in (select user_id from exam_user_role where role_id=25)";
		List<UserJDBC> userList=jdbcTemplate.query(sql,new RowMapper<UserJDBC>(){
			public UserJDBC mapRow(ResultSet rs,int num) throws SQLException{
				UserJDBC userJDBC=new UserJDBC();
				userJDBC.setUserId(rs.getString("user_id"));
				userJDBC.setUserName(rs.getString("user_name"));
				userJDBC.setGender(rs.getInt("gender"));
				userJDBC.setBirthday(rs.getDate("birthday"));
				userJDBC.setEmail(rs.getString("email"));
				userJDBC.setTel(rs.getString("tel"));
				userJDBC.setAddress(rs.getString("address"));
				
				return userJDBC;
			}
		});
				return userList;
	}
	
		
	//新增方法	
	public Integer createUser(UserJDBC userJDBC) throws Exception{
		String sql="insert into exam_user values (?,?,'123',?,?,?,?,?,null,'0')";
		return jdbcTemplate.update(sql,new Object[]{userJDBC.getUserId(),userJDBC.getUserName(),userJDBC.getTel(),userJDBC.getEmail(),userJDBC.getAddress(),userJDBC.getBirthday(),userJDBC.getGender()});
	}
	
	
	//修改方法	
	public Integer editUser(UserJDBC userJDBC) throws Exception{
		String sql="update exam_user set user_name=?,gender=?,birthday=?,tel=?,email=?,address=? where user_id=?";
		return jdbcTemplate.update(sql,new Object[]{userJDBC.getUserName(),userJDBC.getGender(),userJDBC.getBirthday(),userJDBC.getTel(),userJDBC.getEmail(),userJDBC.getAddress(), userJDBC.getUserId()});
	}
	
		
	public Integer deleteUser(String userId) throws Exception{
		String sql="delete from exam_user where user_id=?";
		return jdbcTemplate.update(sql,new Object[]{userId});
	}


	
	public void testFindAllUsers4Mapper() {
		
		
	}

//获取用户-角色表的对象
	@Override
	public ExamUserRoleVO getExamUserRole(String userId) {
		String sql=" select * from exam_user_role where user_id=?";
		return jdbcTemplate.queryForObject(sql,new Object[]{userId},new RowMapper<ExamUserRoleVO>(){
			public ExamUserRoleVO mapRow(ResultSet rs,int num) throws SQLException{
				ExamUserRoleVO examUserRoleVO=new ExamUserRoleVO();
				examUserRoleVO.setRoleId(rs.getInt("role_id"));
				examUserRoleVO.setUserId(rs.getString("user_id"));	
				return examUserRoleVO;
			}
		});
	}

	@Override
	public void updateUserLoginFailCount(UserJDBC user) {
		if(user!=null){
			String id=user.getUserId();
			Integer login_fail_count=user.getLoginFailCount();
			Query query=this.createQuery("update UserJDBC set login_fail_count=? where user_id=?");
			query.setInteger(0, login_fail_count);
			query.setString(1, id);
			query.executeUpdate();
		}
		
	}

	@Override
	public void updateUserLastLockTime(UserJDBC user) {
		if(user!=null){
			String id=user.getUserId();
			Timestamp last_lock_time= user.getLastLockTime();
			Query query=this.createQuery("update UserJDBC set last_lock_time=? where user_id=?");
			//query.setDate(0,last_lock_time);
			query.setTimestamp(0, last_lock_time);
			query.setString(1, id);
			query.executeUpdate();
		}
		
	}

	@Override
	public List<UserJDBC> getAllStudent() {
		String sql="SELECT * FROM exam_user WHERE user_id LIKE '%student%'";
		//String sql="select * from exam_user where user_id in (select user_id from exam_user_role where role_id=25)";
		List<UserJDBC> userList=jdbcTemplate.query(sql,new RowMapper<UserJDBC>(){
			public UserJDBC mapRow(ResultSet rs,int num) throws SQLException{
				UserJDBC userJDBC=new UserJDBC();
				userJDBC.setUserId(rs.getString("user_id"));
				userJDBC.setUserName(rs.getString("user_name"));
				userJDBC.setGender(rs.getInt("gender"));
				userJDBC.setBirthday(rs.getDate("birthday"));
				userJDBC.setEmail(rs.getString("email"));
				userJDBC.setTel(rs.getString("tel"));
				userJDBC.setAddress(rs.getString("address"));
				
				return userJDBC;
			}
		});
				return userList;
		
	}


	
	
	
}
